﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class DSTGBMOVData : BaseFunction
    {

        byte[] b;
        string value;
        public DSTGBMOVData()
            : base()
        {

        }
        public DSTGBMOVData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
       /* public void UpdateWastamm()
        {
            string TableName = "GBMOV";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "update WASTAMM set wavar=wavwvar ,wabez=wabez, warkz='J',warstakz='A', "
                    + " WArfertkz='Z',WAWAART='GEW',WARVOLL='P',warstdlg=wafstdlg,warstdgw=wafstdgw, "
                    + " wagfkz='J',WARwbrt=wafbrtl,wargrqm=wafgrqm,wargrlfd=wafgrlfd,wargwtol='200',warfddik=waffddik, "
                    + " warfddis=waffddis,Wavwart=wadart,waffertkz='E',wafrbst='C',wakavar=' ',wafsubst='       ', "
                    + " wamatprf='N',wabrtprf='N',wagqmprf='N',wastkgp='N',waschlip='N' "
                    + " where  waart like '%X%' ";
                //orlReadCommand.Parameters.Add("ORDER", OracleType.NVarChar).Value = dwUI["ORDER"].ToString().Trim();
                //WBBBESCHICH
                setResult = Connect2Proxy(orlReadCommand);
                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }*/
        /*"SELECT A.GABBUCHDAT,A.GABART,A.GABVAR,A.GABFARBE,A.GABPARTIE, "
                    + " D.GPLIEFSCH,D.GPSPPART,A.GABKG/1000 AS KG,A.GABSP,TRIM(B.GLBEM) AS CO, "
                    + " C.KDKUNDNA,I.PRICE,A.GABKG*0.001*I.PRICE AS VALUE,J.LSNAME1 "
                    + " FROM GABEW A,GPDAT D,LAGPL E,GABEW F,LAGPL G, GLLG B,LIEFDAT J, "
                    + " (SELECT distinct A.VKPMANDANT,TRIM(A.VKPAUFKRS) || '-' || TO_NUMBER(A.VKPAUFLFD) AS CO,B.KDKUNDNA "
                    + "    FROM VKPOS A,KDSTAMM B WHERE A.VKPMANDANT = B.KDMANDANT "
                    + "    AND A.VKPKUNDNR = B.KDKUNDNR AND A.VKPSA <>'AJ') C, "
                    + "   (select A.EKPMANDANT,A.EKPAUFKRS,A.EKPAUFLFD,A.EKPAUFPOS,A.EKPPRME*0.001  AS PRICE "
                    + "    from ekpos A where A.EKPSA = 'PO' AND A.EKPAUFART  = 'BE' "
                    + "    UNION ALL    SELECT A.EKSTPMANDANT,A.EKSTPAUFKRS,A.EKSTPAUFLFD,A.EKSTPAUFPOS, "
                    + "    A.EKSTPPRME*0.001 AS PRICE FROM EKSTPOS A WHERE A.EKSTPAUFART = 'BE') I "
                    + " WHERE A.GABMANDANT = D.GPMANDANT AND A.GABPARTIE = D.GPPARTIE "
                    + " AND A.GABPARTIEKZ = D.GPPARTIEKZ AND A.GABMANDANT = E.LPMANDANT "
                    + " AND A.GABLO = E.LPLO AND A.GABLB = E.LPLB "
                    + " AND A.GABLP = E.LPPLATZ AND A.GABMANDANT = F.GABMANDANT "
                    + " AND A.GABJJJJ = F.GABJJJJ AND A.GABBELEG = F.GABBELEG "
                    + " AND A.GABPARTIE = F.GABPARTIE AND A.GABPARTIEKZ = F.GABPARTIEKZ "
                    + " AND F.GABMANDANT = G.LPMANDANT AND F.GABLO = G.LPLO "
                    + " AND F.GABLB = G.LPLB AND F.GABLP = G.LPPLATZ "
                    + " AND F.GABMANDANT = B.GLMANDANT AND F.GABPARTIE = B.GLPARTIE "
                    + " AND F.GABPARTIEKZ = B.GLPARTIEKZ AND F.GABLBEHI = B.GLLBEHI "
                    + " AND B.GLMANDANT = C.VKPMANDANT(+) AND TRIM(B.GLBEM) = C.CO(+) "
                    + " AND D.GPMANDANT = I.EKPMANDANT(+) AND D.GPBESTKRS = I.EKPAUFKRS(+) "
                    + " AND D.GPBESTLFD = I.EKPAUFLFD(+) AND D.GPBESTPOS = I.EKPAUFPOS(+) "
                    + " AND D.GPMANDANT = J.LSMANDANT(+) AND D.GPLIEFNR = J.LSLIEFNR(+) "
                    + " AND E.LPLBKZ = 'L' AND G.LPLBKZ = 'P' AND A.GABBS = '20' "
                    + " AND F.GABBS = '21' AND A.GABAUFLFD =0 AND A.GABLB = 'YS' "
                    + " AND A.GABBUCHDAT >= '" + WBSRTDATE + "' AND A.GABBUCHDAT <= '" + WBENTDATE + "' "
                    + " AND ((TRIM(B.GLBEM) = '" + ORDER + "') OR ('" + ORDER + "'= 'aaaaaaaa' )) "
                    + " union all "
                    + " SELECT A.GABBUCHDAT,A.GABART,A.GABVAR,A.GABFARBE,A.GABPARTIE, "
                    + " D.GPLIEFSCH,D.GPSPPART,-A.GABKG/1000 AS KG,A.GABSP,TRIM(B.GLBEM) AS CO, "
                    + " C.KDKUNDNA,I.PRICE,-A.GABKG*0.001*I.PRICE AS VALUE,J.LSNAME1 "
                    + " FROM GABEW A,GPDAT D,LAGPL E,GABEW F,LAGPL G, GLLG B,LIEFDAT J, "
                    + " (SELECT distinct A.VKPMANDANT,TRIM(A.VKPAUFKRS) || '-' || TO_NUMBER(A.VKPAUFLFD) AS CO,B.KDKUNDNA "
                    + "    FROM VKPOS A,KDSTAMM B WHERE A.VKPMANDANT = B.KDMANDANT "
                    + "    AND A.VKPKUNDNR = B.KDKUNDNR AND A.VKPSA <>'AJ') C, "
                    + "   (select A.EKPMANDANT,A.EKPAUFKRS,A.EKPAUFLFD,A.EKPAUFPOS,A.EKPPRME*0.001  AS PRICE "
                    + "    from ekpos A where A.EKPSA = 'PO' AND A.EKPAUFART  = 'BE' "
                    + "    UNION ALL    SELECT A.EKSTPMANDANT,A.EKSTPAUFKRS,A.EKSTPAUFLFD,A.EKSTPAUFPOS, "
                    + "    A.EKSTPPRME*0.001 AS PRICE FROM EKSTPOS A WHERE A.EKSTPAUFART = 'BE') I "
                    + " WHERE A.GABMANDANT = D.GPMANDANT AND A.GABPARTIE = D.GPPARTIE "
                    + " AND A.GABPARTIEKZ = D.GPPARTIEKZ AND A.GABMANDANT = E.LPMANDANT "
                    + " AND A.GABLO = E.LPLO AND A.GABLB = E.LPLB "
                    + " AND A.GABLP = E.LPPLATZ AND A.GABMANDANT = F.GABMANDANT "
                    + " AND A.GABJJJJ = F.GABJJJJ AND A.GABBELEG = F.GABBELEG "
                    + " AND A.GABPARTIE = F.GABPARTIE AND A.GABPARTIEKZ = F.GABPARTIEKZ "
                    + " AND F.GABMANDANT = G.LPMANDANT AND F.GABLO = G.LPLO "
                    + " AND F.GABLB = G.LPLB AND F.GABLP = G.LPPLATZ "
                    + " AND F.GABMANDANT = B.GLMANDANT AND F.GABPARTIE = B.GLPARTIE "
                    + " AND F.GABPARTIEKZ = B.GLPARTIEKZ AND F.GABLBEHI = B.GLLBEHI "
                    + " AND B.GLMANDANT = C.VKPMANDANT(+) AND TRIM(B.GLBEM) = C.CO(+) "
                    + " AND D.GPMANDANT = I.EKPMANDANT(+) AND D.GPBESTKRS = I.EKPAUFKRS(+) "
                    + " AND D.GPBESTLFD = I.EKPAUFLFD(+) AND D.GPBESTPOS = I.EKPAUFPOS(+) "
                    + " AND D.GPMANDANT = J.LSMANDANT(+) AND D.GPLIEFNR = J.LSLIEFNR(+) "
                    + " AND E.LPLBKZ = 'L' AND G.LPLBKZ = 'P' AND A.GABBS = '21' "
                    + " AND F.GABBS = '20' AND A.GABAUFLFD =0 AND A.GABLB = 'YS' "
                    + " AND A.GABBUCHDAT >= '" + WBSRTDATE + "' AND A.GABBUCHDAT <= '" + WBENTDATE + "' "
                    + " AND ((TRIM(B.GLBEM) = '" + ORDER + "') OR ('" + ORDER + "'= 'aaaaaaaa' )) "*/
        public DataSet GetWBBST(DataRow dwUI)
        {
            string TableName = "GBMOV";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string WBSRTDATE, WBENTDATE,ORDER;
            WBSRTDATE = dwUI["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwUI["WBENTDATE"].ToString().Trim();
            ORDER = dwUI["ORDER"].ToString().Trim();
            try
            {
                 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.GABBUCHDAT,A.GABART,A.GABVAR,A.GABFARBE,A.GABPARTIE, "
                    + " A.GPLIEFSCH,A.GPSPPART,A.KG,A.GABSP,A.CO, "
                    + " A.KDKUNDNA,A.PRICE,A.VALUE,A.LSNAME1 "
                    + " FROM ISSUE_YARN A WHERE A.GABBUCHDAT >= '" + WBSRTDATE + "' AND A.GABBUCHDAT <= '" + WBENTDATE + "' "
                    + " AND ((A.CO = '" + ORDER + "') OR ('" + ORDER + "'= 'aaaaaaaa' )) "
                    ;
                //orlReadCommand.Parameters.Add("ORDER", OracleType.NVarChar).Value = dwUI["ORDER"].ToString().Trim();
                //WBBBESCHICH
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName; 
                    
                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "KDKUNDNA";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "LSNAME1";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

        public DataSet BuildData(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetWBBST(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}